<?php
/**
 * 统计报表类类
 */
class AcpStatisticalAction extends AcpAction
{
	public function _initialize()
	{
		parent::_initialize();
	}

	//园区管理统计
	public function get_incubator_park_list(){


		$act = I('act');
		//初始化2种where ,$where用于查询所有园区,$where1用于查询合同．
		$where  = 'true';
		$where1 = 'end_time > '.time();
		$station_obj = new StationModel();
		$contract_obj = new ContractModel();
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where1 .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}
			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where1 .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}

			//孵化器id
			$incubator_id = I('incubator_id',0,'int');
			if($incubator_id){
				$where .= ' AND incubator_id ='.$incubator_id;
				//所有合同包含该园区下的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 1 AND FIND_IN_SET('.$incubator_id.', incubator_ids)','station_ids');
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));
				
				//该园区下所有房间id;
				$station_all_arr = $station_obj->getStationFields('incubator_id ='.$incubator_id,'station_type_id');
				
				//对比取出相同的房间id
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('incubator_id',$incubator_id);

				$storey_list = M('Storey')->where('incubator_id ='.$incubator_id)->select();
				$this->assign('storey_list', $storey_list);
			}
			//楼层id
			$storey_id = I('storey_id',0,'int');
			if($storey_id){
				$where .= ' AND storey_id ='.$storey_id;
				//所有合同包含该楼层下的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 1 AND FIND_IN_SET('.$storey_id.', storey_ids)','station_ids');
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));

				//该楼层下所有房间id;
				$station_all_arr = $station_obj->getStationFields('storey_id ='.$storey_id,'station_type_id');
				//对比取出相同的房间id
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('storey_id',$storey_id);


				$station_list = M('StationType')->where('storey_id ='.$storey_id. ' and incubator_id ='.$incubator_id)->select();
				$this->assign('station_list', $station_list);
			}

			//房间/工位id
			$station_type_id = I('station_type_id',0,'int');
			if($station_type_id){
				$where .= ' AND station_type_id ='.$station_type_id;
				//所有合同包含该房间的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 1 AND FIND_IN_SET('.$station_type_id.', station_ids)','station_ids');
				
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));
				//该房间id;
				$station_all_arr = $station_obj->getStationFields('station_type_id ='.$station_type_id,'station_type_id');
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('station_type_id',$station_type_id);
			}

		}
		if(!$station_ids_str){
			$station_ids_str = '0';
		}
		//总面积数
		$station_sum = $station_obj->where($where)->sum('covered_area');
		// $contract_sum = $station_obj->where('station_type_id in('.$station_ids_str.')')->sum('covered_area');

		if($incubator_id || $storey_id || $station_type_id){
			$contract_sum = $station_obj->where('station_type_id in('.$station_ids_str.')')->sum('covered_area');
			// echo $station_obj->getLastSql();die;
		}else{
			//未到期合同,总面积(已租面积)
			$contract_sum = $contract_obj->where($where1)->sum('total_area');
			// echo $contract_obj->getLastSql();die;
		}
		if(!$contract_sum) $contract_sum = 0;
		//剩余面积
		$surplus_area = sprintf("%.2f",$station_sum - $contract_sum);
		//去化率
		$area_rate = sprintf("%.2f",$contract_sum / $station_sum);
		//入住企业数量
		$contract_count = $contract_obj->where($where.' AND end_time >'.time())->count();

		$incubator_obj = new IncubatorModel();
		$incubator_obj->setlimit(100000);
		$incubator_list = $incubator_obj->getIncubatorParkList();
		$this->assign('incubator_list',$incubator_list);

		$year = $this->_post('year');
		if ($year)
		{
			$start_time = mktime(0, 0, 0, 1, 1, $year);
			$end_time = mktime(0, 0, 0, 1, 1, $year + 1) - 1;
			$date = date('Y-m-d', strtotime($year));
		}
		else
		{
			$year = date('Y');
			$start_time = mktime(0, 0, 0, 1, 1, $year);
			$end_time = mktime(0, 0, 0, 1, 1, $year + 1) - 1;
		}
		$this->assign('year', $year);
		//退租
		$deposit_obj = new DepositModel();
		// $deposit_stat_list = $deposit_obj->field('DATE_FORMAT(FROM_UNIXTIME(end_time), "%m") AS month, contract_id')->where('end_time >= ' . $start_time . ' AND end_time <= ' . $end_time)->order('month asc')->select();
		$obj = M();
		$deposit_stat_list = $obj->query('SELECT DATE_FORMAT( FROM_UNIXTIME(tp_deposit.end_time), "%m" ) AS month, sum(tp_contract.total_area) as t_area, count(*) as c_num FROM tp_deposit, tp_contract WHERE tp_deposit.contract_id = tp_contract.contract_id and tp_deposit.end_time >= '.$start_time.' AND tp_deposit.end_time <= '.$end_time.' and tp_contract.contract_type =1 GROUP BY month;');
		
		$new_deposit_stat_list = array();
		$new_company_stat_list = array();
		for ($i = 0; $i < 12; $i ++)
		{
			$new_deposit_stat_list[$i] = 0;
			$new_company_stat_list[$i] = 0;
		}

		//组成数组
		foreach ($deposit_stat_list AS $key => $val)
		{
			$new_deposit_stat_list[intval($val['month'])] = $val['t_area'];
			$new_company_stat_list[intval($val['month'])] = $val['c_num'];
		}
		 // dump($new_deposit_stat_list);
		 // dump($new_company_stat_list);
		$this->assign('new_deposit_stat_list', $new_deposit_stat_list);
		$this->assign('new_company_stat_list', $new_company_stat_list);
		$this->assign('contract_count',$contract_count);
		$this->assign('area_rate',$area_rate);
		$this->assign('surplus_area',$surplus_area);
		$this->assign('station_sum',$station_sum);
		$this->assign('contract_sum',$contract_sum);
		$this->assign('head_title','园区管理统计');
		$this->display();
	}
	//众创空间管理统计
	public function get_incubator_zhongc_list(){

		$incubator_id = I('incubator_id',0,'int');
		$storey_id = I('storey_id',0,'int');
		$station_class_id = I('station_class_id',0,'int');
		$act = I('act');
		$where  = 'true';
		$where1 = 'end_time > '.time();
		$station_obj = new StationModel();
		$contract_obj = new ContractModel();
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where1 .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}
			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where1 .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}

			//孵化器id
			$incubator_id = I('incubator_id',0,'int');
			if($incubator_id){
				$where .= ' AND incubator_id ='.$incubator_id;
				//所有合同包含该园区下的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 2 AND FIND_IN_SET('.$incubator_id.', incubator_ids)','station_ids');
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));
				// dump($station_ids_arr);
				//该园区下所有房间id;
				$station_all_arr = $station_obj->getStationFields('incubator_id ='.$incubator_id,'station_type_id');
				// dump($station_all_arr);
				//对比取出相同的房间id
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// dump($station_ids_str);
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('incubator_id',$incubator_id);

				$storey_list = M('Storey')->where('incubator_id ='.$incubator_id)->select();
				$this->assign('storey_list', $storey_list);
			}
			//楼层id
			$storey_id = I('storey_id',0,'int');
			if($storey_id){
				$where .= ' AND storey_id ='.$storey_id;
				//所有合同包含该楼层下的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 2 AND FIND_IN_SET('.$storey_id.', storey_ids)','station_ids');
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));

				//该楼层下所有房间id;
				$station_all_arr = $station_obj->getStationFields('storey_id ='.$storey_id,'station_type_id');
				//对比取出相同的房间id
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('storey_id',$storey_id);


				$station_class_list = M('StationClass')->where('incubator_id ='.$incubator_id)->select();
				$this->assign('station_class_list', $station_class_list);
			}

			//工位类型id
			$station_class_id = I('station_class_id',0,'int');
			if($station_class_id){
				$where .= ' AND station_class_id ='.$station_class_id;
				//所有合同包含该房间的房间id;
				$station_ids_arr = $contract_obj->getContractWhereFields($where1.' AND contract_type = 2','station_ids');
				// dump($station_ids_arr);
				$station_ids_arr[] = 0;
				$station_ids_arr = explode(',', implode(',', $station_ids_arr));
				//类型下所有工位
				$station_all_arr = $station_obj->where('incubator_id ='.$incubator_id.' and station_class_id ='.$station_class_id .' and storey_id ='.$storey_id)->getField('station_type_id', true);
				// echo $station_obj->getLastSql();
				// dump($station_all_arr);
				$station_ids_str = implode(',',array_intersect($station_ids_arr,$station_all_arr));
				// dump($station_ids_str);
				// $where1 .= ' AND station_type_id in ('.$station_ids_str.')';
				$this->assign('station_class_id',$station_class_id);
			}

		}
		if(!$station_ids_str){
			$station_ids_str = array();
		}


		$incubator_obj = new IncubatorModel();
		$incubator_obj->setLimit(10000);
		$incubator_list = $incubator_obj->getIncubatorZhongcList();
		$this->assign('incubator_list',$incubator_list);

		$station_obj = new StationModel();
		//总工位数
		// $station_list = $station_obj->get_statistical_list('','','sum(station_total_num) as sum_station,sum(covered_area) as sum_area');
		$total_num = $station_obj->getStationNum($where .' and types = 2');

		if($incubator_id || $storey_id || $station_type_id){
			// $contract_station_num = $station_obj->where('station_type_id in('.$station_ids_str.')')->select();
			$contract_station_num = count(explode(',', $station_ids_str));
			// dump($contract_station_num);die;
			// echo $station_obj->getLastSql();die;
		}else{
			//未到期合同,总面积, 总工位
			$contract_obj = new ContractModel();
			$contract_station_num = $contract_obj->where('end_time >'.time() .' and contract_type = 2' )->sum('total_station_num');
		}
		


		// dump($station_list);
		// dump($contract_list);die;
		$this->assign('contract_station_num', $contract_station_num);
		$this->assign('total_station_num', $total_num);
		$this->assign('left_station_num', $total_num - $contract_station_num);
		$this->display();
	}

	//去化率统计
	public function get_selling_list(){


		$station_obj = new StationModel();
		$contract_obj = new ContractModel();

		$act = I('act');
		$where = ' end_time > '.time();
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where .= ' AND end_time >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where .= ' AND end_time <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}

		//总面积
		$station_list = $station_obj->get_statistical_list('','','sum(covered_area) as sum_area');
		//总工位
		$station_total_num = $station_obj->getStationNum('types = 2');
		//合同工位,合同面积
		$contract_list = $contract_obj->get_statistical_list($where,'',' sum(total_area) as lease_sum_area , sum(total_station_num) as lease_sum_station');
		//工位率
		$station_rate = sprintf("%.2f",$contract_list[0]['lease_sum_station'] / $station_total_num);;
		//面积率
		$area_rate = sprintf("%.2f",$contract_list[0]['lease_sum_area'] / $station_list[0]['sum_area']);

		$this->assign('station_rate',$station_rate);
		$this->assign('area_rate',$area_rate);
		$this->assign('head_title','去化率统计');
		$this->display();
	}

	//每月成交面积/工位统计()
	public function get_month_station_list(){

		$contract_obj = new ContractModel();
		$where ='true';
		$act = I('act');
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}

		$contract_list = $contract_obj->get_statistical_list($where,'',' sum(total_area) as sum_area , sum(total_station_num) as sum_station');

		$this->assign('contract_list',$contract_list);
		$this->assign('head_title','每月成交的面积和工位');
		$this->display();
	}

	//租期统计(合同数,面积数,房间数)
	public function get_tenancy_list(){

		$contract_obj = new ContractModel();
		$act = I('act');
		$where = ' end_time < '.time();
		if($act == 'search'){

			
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$end_date = $this->_request('end_date');
			if($start_date || $end_date){
				$where = 'true';
			}else{
				$where = ' end_time < '.time();
			}

			if ($start_date) {
				$start_date = str_replace('+', ' ', $start_date);
				$start_date = strtotime($start_date);
				$where .= ' AND end_time >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			
			if ($end_date) {
				$end_date = str_replace('+', ' ', $end_date);
				$end_date = strtotime($end_date);
				$where .= ' AND end_time <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}

		$contract_list = $contract_obj->get_statistical_list($where,'','count(*) as contract_num , sum(total_area) as sum_area , sum(total_rooms) as sum_rooms');
		$this->assign('contract_list',$contract_list);
		$this->assign('head_title','合同数,房间数,面积数');
		$this->display();
	}

	//入住企业数所属行业的占比
	public function get_field_area_list(){


		$act = I('act');
		$where = ' end_time >'.time();
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			
			if ($start_date) {
				$start_date = str_replace('+', ' ', $start_date);
				$start_date = strtotime($start_date);
				$where .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			
			if ($end_date) {
				$end_date = str_replace('+', ' ', $end_date);
				$end_date = strtotime($end_date);
				$where .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}
		$contract_obj = new ContractModel();
		$contract_list = $contract_obj ->get_statistical_list($where,'type_id ','type_id,total_area ,count(type_id) as type_ids , sum(total_area) as total_areas');
		foreach ($contract_list as $k => $v){
			$type_obj = new TypeModel();
			$contract_list[$k]['field_name'] = $type_obj->getTypeSiteField('type_name','type_id ='.$v['type_id']);
		}
		
		$this->assign('contract_list',$contract_list);
		$this->assign('head_title','入住企业数所属行业的占比数');
		$this->display();
	}

	//退租原因统计
	public function get_surrender_list(){

		$act = I('act');
		$deposit_obj = new DepositModel();
		$where = 'true';
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where .= ' AND end_time >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where .= ' AND end_time <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}
		$deposit_list = $deposit_obj->getStatisticalList($where,'surrender','surrender,count(surrender) as count');

		foreach($deposit_list as $k=>$v){
			$type_obj = new TypeModel();
			$deposit_list[$k]['surrender_name'] = $type_obj->getTypeSiteField('type_name','type_id ='.$v['surrender']);
		}

		$this->assign('deposit_list',$deposit_list);
		$this->assign('head_title','退租原因统计');
		$this->display();
	}

	//流失原因统计
	public function get_drain_list(){

		$act = I('act');
		$where = 'follow_state = 5';
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}
			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}
		$talk_summary_obj = new TalkSummaryModel();
		$talk_summary_list =$talk_summary_obj->get_statistical_list($where,'drain_why','drain_why,count(*) as count');
		foreach($talk_summary_list as  $k => $v){
			$type_obj = new TypeModel();
			$talk_summary_list[$k]['drain_name'] = $type_obj->getTypeSiteField('type_name','type_id ='.$v['drain_why']);
		}

		$this->assign('drain_list',$talk_summary_list);
		$this->assign('head_title','流失原因统计');
		$this->display();
	}

	//渠道来源统计
	public function get_source_list(){

		$act = I('act');
		$where = 'true';
		if($act == 'search'){
			//添加时间范围起始时间
			$start_date = $this->_request('start_date');
			$start_date = str_replace('+', ' ', $start_date);
			$start_date = strtotime($start_date);
			if ($start_date) {
				$where .= ' AND addtime >= ' . $start_date;
				$this->assign('start_date',$start_date);
			}

			//添加时间范围结束时间
			$end_date = $this->_request('end_date');
			$end_date = str_replace('+', ' ', $end_date);
			$end_date = strtotime($end_date);
			if ($end_date) {
				$where .= ' AND addtime <= ' . $end_date;
				$this->assign('end_date',$end_date);
			}
		}

		$customer_obj = new CustomerModel();
		$source_list = $customer_obj->get_statistical_list($where,'source','source,count(*) as count');
		foreach($source_list as  $k => $v){
			$type_obj = new TypeModel();
			$source_list[$k]['source_name'] = $type_obj->getTypeSiteField('type_name','type_id ='.$v['source']);
		}

		$this->assign('source_list',$source_list);
		$this->assign('head_title','渠道来源统计');
		$this->display();
	}

}
?>
